# $Id: sql.py 9227e81e919a 2012/08/27 15:49:57 pgurumur $
# Copyright (c) 2009 Prabhu Gurumurthy  <pgurumur@gmail.com>
# 
# Permission to use, copy, modify, and distribute this software for any
# purpose with or without fee is hereby granted, provided that the above
# copyright notice and this permission notice appear in all copies.
#
# THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
# WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
# ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
# WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
# ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
# OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
#

__version__  = "$Revision: 9227e81e919a $"
__author__   = "$Author: pgurumur $"
__modified__ = "$Date: 2012-08-27 15:49:57Z $"

from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy.exc import OperationalError
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from exception import SQLError

MYSQL = 1
SQLITE = 2

class SQLObject(object):
   def __init__(self, Query, Table, Engine):
      super(SQLObject, self).__init__()
      self._table = None
      self._query = None
      self._engine = None

      if Query:
         self._query = Query
      else:
         raise SQLError("No query object given!")

      if Table is not None:
         self._table = Table
      else:
         raise SQLError("No table object given!")

      if Engine:
         self._engine = Engine
      else:
         raise SQLError("No SQL engine defined!")

   def __getattr__(self, name):
      retval = None
      if hasattr(self._table.c, name):
         retval = getattr(self._table.c, name)

      return retval

   def __getitem__(self, key):
      retval = []
      column = getattr(self, key)
      if column is not None:
         for ix in self.q.values(column):
            retval.append(ix[0])

      return retval

   @property
   def _columns(self):
      return self._table.c.keys()

   @property
   def q(self):
      return self._query

   def GetTable(self):
      return self._table

   def SQLInsert(self, **kwargs):
      retval = None
      if kwargs:
         connection = self._engine.connect()
         insert = self._table.insert(values = dict(**kwargs))
         retval = connection.execute(insert)
         connection.close()
         if retval:
            retval = retval.inserted_primary_key[0]

      return retval

   def SQLUpdate(self, key, value, **kwargs):
      retval = None
      if key and value and hasattr(self, str(key)):
         key = getattr(self, str(key))
         connection = self._engine.connect()
         result = connection.execute(self._table.update().where(
            key == value).values(dict(**kwargs)))
         connection.close()
         if result:
            retval = result.last_updated_params()

      return retval

   def __len__(self):
      return len(self.q.all())

   def __str__(self):
      return str(self._table).encode('utf-8')

   def __repr__(self):
      return repr(str(self))

   def __unicode__(self):
      return unicode(str(self))

   def __eq__(self, Name):
      retval = False
      if isinstance(Name, (str, unicode)):
         if str(self._table) == Name:
            retval = True

      return retval

   all = property(fget = lambda self: self.q.all())

class sql(object):
   def __init__(self, Database, Url, Type = 2, Verbose = False):
      self._database = None
      self._session = None
      self._engine = None
      self._meta = MetaData()
      self._base = declarative_base()

      if Database:
         self._database = Database

      if Url:
         self._engine = create_engine(Url, echo = Verbose)
         session = sessionmaker(bind = self._engine)
         self._session = session()
      else:
         raise SQLError("No URL defined to connect!")

   def close(self):
      if self._session:
         self._session.close()

   def __repr__(self):
      return repr(str(self))

   def __unicode__(self):
      return unicode(str(self))

   def _commit(self):
      try:
         self._session.commit()
      except OperationalError as message:
         raise SQLError(message)

   def add(self, Table, **kwargs):
      self._session.add(Table)
      if "commit" in kwargs:
         if kwargs["commit"] == True:
            self._commit()
            

   def delete(self, Table, **kwargs):
      self._session.delete(Table)
      if "commit" in kwargs:
         if kwargs["commit"] == True:
            self._commit()

   def __getattr__(self, name):
      return self[name]

   def __getitem__(self, key):
      retval = None
      if key:
         for table in self.inspect():
            if str(table) == str(key):
               retval = table
               break

      return retval

   @property
   def engine(self):
      return self._engine

   @property
   def session(self):
      return self._session

   def inspect(self):
      retval = []
      tables = []
      if self._engine:
         try:
            self._meta.reflect(bind = self._engine)
            tables = self._meta.sorted_tables
         except OperationalError as message:
            raise SQLError(message)

      for tbl in tables:
         retval.append(SQLObject(self.query(tbl), tbl, self._engine))

      return retval

   @property
   def tables(self):
      retval = None
      if self._engine:
         retval = self._engine.table_names()

      return retval

   def execute(self, Query):
      return self._engine.connect().execute(Query)

   def query(self, Table):
      retval = None
      if Table is not None:
         retval = self._session.query(Table)

      return retval

   def create(self):
      try:
         self._base.metadata.create_all(self._engine)
      except OperationalError as message:
         raise SQLError(message)

class mysql(sql):
   def __init__(self, Database, Host, Username, Password = None,
         Verbose = False):
      url = None
      self._user = Username
      self._host = Host
      self._db = Database
      if Password:
         url = "mysql://%s:%s@%s/%s" %(Username, Password, Host, Database)
      else:
         url = "mysql://%s@%s/%s" %(Username, Host, Database)

      super(mysql, self).__init__(Database, url, MYSQL, Verbose)

   def __str__(self):
      return "mysql handle <%s@%s/%s>".encode('utf-8') %(
            self._user, self._host, self._db)

class sqlite(sql):
   def __init__(self, Database, Verbose = False):
      self._db = Database

      url = "sqlite:///%s" %Database
      super(sqlite, self).__init__(Database, url, SQLITE, Verbose)

   def __str__(self):
      return "sqlite handle <%s>".encode('utf-8') %self._db
